USE [master]
GO

CREATE DATABASE [FxCopStatistics]
GO

USE [FxCopStatistics]
GO

CREATE TYPE [dbo].[path] FROM [nvarchar](500) NOT NULL
GO
CREATE TYPE [dbo].[sid] FROM [varbinary](85) NOT NULL
GO
CREATE TYPE [dbo].[rulecategory] FROM [nvarchar](100) NOT NULL
GO
CREATE TYPE [dbo].[rulename] FROM [nvarchar](200) NOT NULL
GO

CREATE FUNCTION [dbo].[fnZlsToNullN] 
	(
		@Input	nvarchar(max)
	)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @Result	nvarchar(max)
	
	SET @Result = LTRIM(RTRIM(@Input))
	IF (LEN(@Result) = 0) SET @Result = NULL
	
	RETURN @Result
END
GO

CREATE PROCEDURE [dbo].[spRethrowError]
AS
BEGIN
    -- Return if there is no error information to retrieve.
    IF ERROR_NUMBER() IS NULL RETURN

    DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200)

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE()

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
        )
END
GO

GRANT EXEC ON dbo.spRethrowError TO PUBLIC
GO

CREATE TABLE [dbo].[StatisticTypes]
(
	[ID] [int] NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	CONSTRAINT [PK_StatisticTypes] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_StatisticTypes_Description] ON [dbo].[StatisticTypes] 
(
	[Description] ASC
)
GO

CREATE TABLE [dbo].[TodoPrefixes]
(
	[Prefix] [nvarchar](200) NOT NULL,
	 CONSTRAINT [PK_TodoPrefixes_1] PRIMARY KEY CLUSTERED 
		(
			[Prefix] ASC
		)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_TodoPrefixes_Unique] ON [dbo].[TodoPrefixes] 
(
	[Prefix] ASC
)
GO


CREATE TABLE [dbo].[Projects]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[IsActive] [bit] NOT NULL CONSTRAINT [DF_Projects_IsActive]  DEFAULT ((1)),
	[TreatAllExclusionsAsTodos] [bit] NOT NULL,
	CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [UX_Projects] ON [dbo].[Projects] 
(
	[Name] ASC
)
GO

CREATE TABLE [dbo].[AnalysisRuns]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectID] [int] NOT NULL,
	[AnalysisTimestamp] [datetime] NOT NULL,
	[StatisticTypeID] [int] NOT NULL,
	CONSTRAINT [PK_AnalysisRuns] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)
)
GO

CREATE NONCLUSTERED INDEX [IX_AnalysisRuns_Unique] ON [dbo].[AnalysisRuns] 
(
	[ProjectID] ASC,
	[AnalysisTimestamp] ASC,
	[StatisticTypeID] ASC
)
GO

CREATE TABLE [dbo].[ProjectLocations]
(
	[ProjectID] [int] NOT NULL,
	[UserID] [dbo].[sid] NOT NULL,
	[WorkstationID] [nvarchar](50) NOT NULL,
	[ProjectPath] [dbo].[path] NOT NULL,
	[FxCopCmdPath] [dbo].[path] NOT NULL,
	CONSTRAINT [PK_ProjectLocations_1] PRIMARY KEY CLUSTERED 
		(
			[ProjectID] ASC,
			[UserID] ASC,
			[WorkstationID] ASC
		)
)
GO

CREATE TABLE [dbo].[ViolationHistory]
(
	[AnalysisID] [int] NOT NULL,
	[RuleCategory] [dbo].[rulecategory] NOT NULL,
	[RuleName] [dbo].[rulename] NOT NULL,
	[ViolationCount] [int] NOT NULL,
	CONSTRAINT [PK_ViolationHistory] PRIMARY KEY CLUSTERED 
		(
			[AnalysisID] ASC,
			[RuleCategory] ASC,
			[RuleName] ASC
		)
)
GO

CREATE TABLE [dbo].[RuleCategories]
(
	[CategoryName] [dbo].[rulecategory] NOT NULL,
	[GroupName] [dbo].[rulecategory] NULL,
	CONSTRAINT [PK_RuleCategories] PRIMARY KEY CLUSTERED 
		(
			[CategoryName] ASC
		)
)
GO


CREATE TABLE [dbo].[CategoryGroups]
(
	[GroupName] [dbo].[rulecategory] NOT NULL,
	[SortingIndex] [int] NOT NULL,
	CONSTRAINT [PK_CategoryGroups] PRIMARY KEY CLUSTERED 
	(
		[GroupName] ASC
	)
)
GO


CREATE PROCEDURE [dbo].[spDeleteTodoPrefix]
	(
		@Prefix		nvarchar(200)
	)
AS
BEGIN
	SET @Prefix = dbo.fnZlsToNullN(@Prefix)
	
	DELETE FROM dbo.TodoPrefixes
	WHERE Prefix = @Prefix
END
GO

GRANT EXEC ON dbo.spDeleteTodoPrefix TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spSaveTodoPrefix]
	(
		@OldPrefix		nvarchar(200) = NULL,
		@NewPrefix		nvarchar(200)
	)
AS
BEGIN
	SET @OldPrefix = dbo.fnZlsToNullN(@OldPrefix)
	SET @NewPrefix = dbo.fnZlsToNullN(@NewPrefix)
	
	IF @OldPrefix IS NULL
		BEGIN
			INSERT INTO dbo.TodoPrefixes (Prefix)
			VALUES (@NewPrefix)
		END
	ELSE
		BEGIN
			UPDATE dbo.TodoPrefixes
			SET Prefix = @NewPrefix
			WHERE Prefix = @OldPrefix
		END
END
GO

GRANT EXEC ON dbo.spSaveTodoPrefix TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spCreateAnalysisRun] 
	(
		@ProjectID				int,
		@AnalysisTimestamp		datetime,
		@StatisticTypeID		int,
		@AnalysisID				int		OUTPUT
	)
AS
BEGIN
	SET NOCOUNT ON
	
	INSERT INTO dbo.AnalysisRuns (ProjectID, AnalysisTimestamp, StatisticTypeID)
	VALUES (@ProjectID, @AnalysisTimestamp, @StatisticTypeID)
	
	IF @@ERROR = 0 SET @AnalysisID = SCOPE_IDENTITY()
END
GO

GRANT EXEC ON dbo.spCreateAnalysisRun TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spGetAnalysisRuns]
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT DISTINCT ProjectID, AnalysisTimestamp
	FROM AnalysisRuns
	ORDER BY ProjectID, AnalysisTimestamp
END
GO

GRANT EXEC ON dbo.spGetAnalysisRuns TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spGetTodoPrefixes]
AS
BEGIN
	SET NOCOUNT ON		
	
	SELECT	Prefix
	FROM	dbo.TodoPrefixes
	ORDER BY Prefix
END
GO

GRANT EXEC ON dbo.spGetTodoPrefixes TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spSaveProject]
	(
		@ProjectID					int				OUTPUT,
		@ProjectName				nvarchar(100),
		@IsActive					bit,
		@TreatAllExclusionsAsTodos	bit,
		@ProjectPath				dbo.path,
		@FxCopCmdPath				dbo.path
	)
AS
BEGIN
	DECLARE	@UserID			dbo.sid,
			@WorkstationID	nvarchar(50)
			
	SET @UserID = SUSER_SID()
	SET @WorkstationID = HOST_NAME()		

	SET @ProjectName = dbo.fnZlsToNullN(@ProjectName)
	
	BEGIN TRANSACTION
	BEGIN TRY
		IF @ProjectID IS NULL
			BEGIN
				INSERT INTO Projects (Name, IsActive, TreatAllExclusionsAsTodos)
				VALUES (@ProjectName, @IsActive, @TreatAllExclusionsAsTodos)
				
				SET @ProjectID = SCOPE_IDENTITY()
			END
		ELSE
			BEGIN
				UPDATE Projects
				SET Name = @ProjectName,
					IsActive = @IsActive,
					TreatAllExclusionsAsTodos = @TreatAllExclusionsAsTodos
				WHERE ID = @ProjectID
			END
		
		SET @ProjectPath = dbo.fnZlsToNullN(@ProjectPath)
		SET @FxCopCmdPath = dbo.fnZlsToNullN(@FxCopCmdPath)
		IF (@ProjectPath IS NULL) OR (@FxCopCmdPath IS NULL)
			BEGIN
				DELETE FROM dbo.ProjectLocations
				WHERE	(ProjectID = @ProjectID) AND
						(UserID = @UserID) AND
						(WorkstationID = @WorkstationID)
			END
		ELSE IF (SELECT COUNT(*) 
					FROM dbo.ProjectLocations
					WHERE	(ProjectID = @ProjectID) AND
							(UserID = @UserID) AND
							(WorkstationID = @WorkstationID)) = 0
			BEGIN
				INSERT INTO dbo.ProjectLocations (ProjectID, UserId, WorkstationID, ProjectPath, FxCopCmdPath)
				VALUES (@ProjectID, @UserID, @WorkstationID, @ProjectPath, @FxCopCmdPath)
			END
		ELSE
			BEGIN
				UPDATE dbo.ProjectLocations
				SET ProjectPath = @ProjectPath,
					FxCopCmdPath = @FxCopCmdPath
				WHERE	(ProjectID = @ProjectID) AND
						(UserID = @UserID) AND
						(WorkstationID = @WorkstationID)
			END
		
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		EXEC dbo.spRethrowError		
	END CATCH
END
GO

GRANT EXEC ON dbo.spSaveProject TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spAddViolationStatistic] 
	(
		@AnalysisID				int,
		@RuleCategory			dbo.rulecategory,
		@RuleName				dbo.rulename,
		@ViolationCount			int
	)
AS
BEGIN
	SET NOCOUNT ON
	
	SET @RuleCategory = dbo.fnZlsToNullN(@RuleCategory)
	SET @RuleName = dbo.fnZlsToNullN(@RuleName)
	
	INSERT INTO dbo.ViolationHistory (AnalysisID, RuleCategory, RuleName, ViolationCount)
	VALUES (@AnalysisID, @RuleCategory, @RuleName, @ViolationCount)
END
GO

GRANT EXEC ON dbo.spAddViolationStatistic TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spGetProjectByName] 
	(
		@ProjectName		nvarchar(100)
	)
AS
BEGIN
	SET NOCOUNT ON		
	
	SET @ProjectName = dbo.fnZlsToNullN(@ProjectName)

	SELECT	P.ID AS ProjectID, P.TreatAllExclusionsAsTodos, L.ProjectPath, L.FxCopCmdPath
	FROM	Projects AS P
			LEFT JOIN ProjectLocations AS L
				ON	(P.ID = L.ProjectID) AND
					(L.UserID = SUSER_SID()) AND 
					(L.WorkstationID = HOST_NAME())
	WHERE P.Name = @ProjectName
END
GO

GRANT EXEC ON dbo.spGetProjectByName TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spViolationDetails]
	(
		@ProjectID			int,
		@AnalysisTimestamp	datetime,
		@StatisticTypeID	int
	)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @AnalysisID int
	SET @AnalysisID = (SELECT ID
						FROM AnalysisRuns
						WHERE	(ProjectID = @ProjectID) AND
								(AnalysisTimestamp = @AnalysisTimestamp) AND
								(StatisticTypeID = @StatisticTypeID))
	
	SELECT RuleCategory, RuleName, ViolationCount
	FROM dbo.ViolationHistory
	WHERE AnalysisID = @AnalysisID
	ORDER BY RuleCategory, RuleName
END
GO

GRANT EXEC ON dbo.spViolationDetails TO PUBLIC
GO


CREATE VIEW [dbo].[vwRuleCategories]
AS
SELECT	V.RuleCategory, 
		COALESCE(C.GroupName, V.RuleCategory) AS GroupName, 
		COALESCE (G.SortingIndex, (SELECT MAX(SortingIndex) + 1 FROM dbo.CategoryGroups)) AS SortingIndex
FROM	(SELECT DISTINCT RuleCategory
			FROM dbo.ViolationHistory
			UNION SELECT CategoryName
			FROM dbo.RuleCategories) AS V
		LEFT OUTER JOIN dbo.RuleCategories AS C 
			ON V.RuleCategory = C.CategoryName
		LEFT OUTER JOIN dbo.CategoryGroups AS G
			ON C.GroupName = G.GroupName
GO


CREATE PROCEDURE [dbo].[spGetProjects]
	(
		@ID		int
	)
AS
BEGIN
	SET NOCOUNT ON		
	
	SELECT	P.ID AS ProjectID, P.Name AS ProjectName, P.IsActive, P.TreatAllExclusionsAsTodos, 
			L.ProjectPath, L.FxCopCmdPath
	FROM	Projects AS P
			LEFT JOIN ProjectLocations AS L
				ON	(P.ID = L.ProjectID) AND
					(L.UserID = SUSER_SID()) AND 
					(L.WorkstationID = HOST_NAME())
	WHERE	((@ID IS NULL) OR (P.ID = @ID))
	ORDER BY P.Name	
END
GO

GRANT EXEC ON dbo.spGetProjects TO PUBLIC
GO


CREATE PROCEDURE [dbo].[spViolationSummary]
	(
		@ProjectID			int,
		@AnalysisTimestamp	datetime,
		@StatisticTypeID	int
	)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @AnalysisID int
	SET @AnalysisID = (SELECT ID
						FROM AnalysisRuns
						WHERE	(ProjectID = @ProjectID) AND
								(AnalysisTimestamp = @AnalysisTimestamp) AND
								(StatisticTypeID = @StatisticTypeID))
								
	SELECT 
		C.GroupName AS Category,
		COALESCE(SUM(V.ViolationCount), 0) AS ViolationCount,
		MIN(C.SortingIndex) AS SortingIndex
	FROM
		dbo.vwRuleCategories AS C
		LEFT JOIN (SELECT RuleCategory, SUM(ViolationCount) AS ViolationCount
					FROM	dbo.ViolationHistory
					WHERE AnalysisID = @AnalysisID
					GROUP BY RuleCategory) AS V
			ON C.RuleCategory = V.RuleCategory
	GROUP BY C.GroupName
	ORDER BY SortingIndex, Category
END
GO

GRANT EXEC ON dbo.spViolationSummary TO PUBLIC
GO

CREATE PROCEDURE dbo.spDeleteStatistics 
	(
		@ProjectID			int,
		@AnalysisTimestamp	datetime
	)
AS
BEGIN
	SET NOCOUNT ON
	
	DELETE FROM dbo.AnalysisRuns
	WHERE	(ProjectID = @ProjectID) AND
			(AnalysisTimestamp = @AnalysisTimestamp)
END
GO

GRANT EXEC ON dbo.spDeleteStatistics TO PUBLIC
GO


ALTER TABLE [dbo].[ViolationHistory]  WITH CHECK ADD  CONSTRAINT [CK_ViolationHistory_ViolationCount] CHECK  (([ViolationCount]>=(0)))
GO
ALTER TABLE [dbo].[ViolationHistory] CHECK CONSTRAINT [CK_ViolationHistory_ViolationCount]
GO

ALTER TABLE [dbo].[AnalysisRuns]  WITH CHECK ADD  CONSTRAINT [FK_AnalysisRuns_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ID])
GO
ALTER TABLE [dbo].[AnalysisRuns] CHECK CONSTRAINT [FK_AnalysisRuns_Projects]
GO

ALTER TABLE [dbo].[AnalysisRuns]  WITH CHECK ADD  CONSTRAINT [FK_AnalysisRuns_StatisticTypes] FOREIGN KEY([StatisticTypeID])
REFERENCES [dbo].[StatisticTypes] ([ID])
GO
ALTER TABLE [dbo].[AnalysisRuns] CHECK CONSTRAINT [FK_AnalysisRuns_StatisticTypes]
GO

ALTER TABLE [dbo].[ProjectLocations]  WITH CHECK ADD  CONSTRAINT [FK_ProjectLocations_Projects] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Projects] ([ID])
GO
ALTER TABLE [dbo].[ProjectLocations] CHECK CONSTRAINT [FK_ProjectLocations_Projects]
GO

ALTER TABLE [dbo].[RuleCategories]  WITH CHECK ADD  CONSTRAINT [FK_RuleCategories_CategoryGroups] FOREIGN KEY([GroupName])
REFERENCES [dbo].[CategoryGroups] ([GroupName])
GO
ALTER TABLE [dbo].[RuleCategories] CHECK CONSTRAINT [FK_RuleCategories_CategoryGroups]
GO

ALTER TABLE dbo.ViolationHistory ADD CONSTRAINT
	FK_ViolationHistory_AnalysisRuns FOREIGN KEY
	(
	AnalysisID
	) REFERENCES dbo.AnalysisRuns
	(
	ID
	) ON UPDATE  NO ACTION 
	 ON DELETE  CASCADE 
GO


ALTER TABLE [dbo].[ViolationHistory] CHECK CONSTRAINT [FK_ViolationHistory_AnalysisRuns]
GO


INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Design', 1)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Globalization', 2)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Interoperability', 3)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Maintainability', 4)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Mobility', 5)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Naming', 6)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Performance', 7)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Portability', 8)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Reliability', 9)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Security', 10)

INSERT INTO dbo.CategoryGroups (GroupName, SortingIndex)
VALUES ('Usage', 11)
GO

INSERT INTO dbo.RuleCategories (CategoryName, GroupName)
SELECT 'Microsoft.' + GroupName, GroupName
FROM dbo.CategoryGroups
GO

INSERT INTO dbo.StatisticTypes (ID, Description)
VALUES (1, 'Total')

INSERT INTO dbo.StatisticTypes (ID, Description)
VALUES (2, 'Todo')
GO
